# -*- coding: UTF-8 -*-
# @Project ：VehicleDimensionMeasurementSystem 
# @File    ：db_operation.py
# @IDE     ：PyCharm 
# @Date    ：2024/6/11 下午10:13
import traceback
from loguru import logger
from datetime import datetime, timedelta
from sqlalchemy import asc, desc, select, and_, or_
from sqlalchemy.exc import NoResultFound
from sqlalchemy.orm import joinedload

from source.database.models import (Session, PlateTypeModel, VehicleInfoModel, VehicleClassModel, VehicleTypeModel,
                                    MeasureRecordModel, PlateAbbreviationModel, InspectionCategoryModel)


# 定义一个查询类
class CustomDBException(Exception):
    """自定义数据库操作异常"""

    def __init__(self, message, status_code=None):
        super().__init__(message)
        self.status_code = status_code  # 状态码


class InitializationUiQuery:
    """初始化界面需要的数据查询"""
    session = Session()

    def query_plate_abbreviations(self):
        """查询号牌种类简称"""
        query_set = self.session.query(PlateAbbreviationModel.plate_abbr).filter(
            PlateAbbreviationModel.is_show == 1).all()
        return [query.plate_abbr for query in query_set]

    def query_plate_type(self):
        """查询号牌种类"""
        query_set = self.session.query(PlateTypeModel.plate_type).filter(PlateTypeModel.is_show == 1).all()
        return [query.plate_type for query in query_set]

    def query_vehicle_type(self):
        """查询车辆类型"""
        query_set = self.session.query(VehicleTypeModel.vehicle_type).order_by(desc(VehicleTypeModel.sort_show)).all()
        return [query.vehicle_type for query in query_set]

    def query_inspection_category(self):
        """查询检验类别"""
        query_set = self.session.query(InspectionCategoryModel.inspection_category).all()
        return [query.inspection_category for query in query_set]

    def query_vehicle_class(self):
        """查询车辆所属类型"""
        query_set = self.session.query(VehicleClassModel.vehicle_class).all()
        return [query.vehicle_class for query in query_set]


class InsertData:
    session = Session()

    def insert_vehicle_info(self, vehicle_info):
        def data_clean(filed_object):
            """对数据进行清洗"""
            try:
                data = filed_object.get_text
                return data if data else None
            except Exception as e:
                data = None
                return data

        plate_type_row = self.session.query(PlateTypeModel.id).filter(
            PlateTypeModel.plate_type == vehicle_info.plate_type.get_text).first()
        vehicle_type_row = self.session.query(VehicleTypeModel.id).filter(
            VehicleTypeModel.vehicle_type == vehicle_info.vehicle_type.get_text).one()
        vehicle_class_row = self.session.query(VehicleClassModel.id).filter(
            VehicleClassModel.vehicle_class == vehicle_info.vehicle_class.get_text).one()
        vehicle_info_dict = {
            'serial_number': data_clean(vehicle_info.serial_number),
            'vin': data_clean(vehicle_info.vin),
            'plate_type_id': plate_type_row.id,
            'plate_number': data_clean(vehicle_info.plate_number),
            'vehicle_type_id': vehicle_type_row.id,
            'vehicle_class_id': vehicle_class_row.id,
            'vehicle_model': data_clean(vehicle_info.vehicle_model),
            'total_mass': data_clean(vehicle_info.total_mass),
            'brand': data_clean(vehicle_info.brand),
            'body_color': data_clean(vehicle_info.body_color),
            'number_axes': data_clean(vehicle_info.number_axes),
            'vehicle_owner': data_clean(vehicle_info.vehicle_owner),
            'date_first_registration': data_clean(vehicle_info.date_first_registration),
            'manufacturer': data_clean(vehicle_info.manufacturer),
            'standard_length': data_clean(vehicle_info.vehicle_standard_length),
            'standard_width': data_clean(vehicle_info.vehicle_standard_width),
            'standard_height': data_clean(vehicle_info.vehicle_standard_height),
            'limit_length': data_clean(vehicle_info.limit_length),
            'limit_width': data_clean(vehicle_info.limit_width),
            'limit_height': data_clean(vehicle_info.limit_height),
            'standard_wheelbase': data_clean(vehicle_info.vehicle_standard_wheelbase),
            'standard_fence_depth': data_clean(vehicle_info.vehicle_standard_fence_depth),
            'standard_curb_weigh': data_clean(vehicle_info.vehicle_standard_curb_weigh),
            'cargo_hold_standard_length': data_clean(vehicle_info.cargo_hold_standard_length),
            'cargo_hold_standard_width': data_clean(vehicle_info.cargo_hold_standard_width),
            'cargo_hold_standard_height': data_clean(vehicle_info.cargo_hold_standard_height),
            'tank_standard_length': data_clean(vehicle_info.tank_standard_length),
            'tank_standard_width': data_clean(vehicle_info.tank_standard_width),
            'tank_standard_height': data_clean(vehicle_info.tank_standard_height),
            'tank_standard_volume': data_clean(vehicle_info.tank_standard_volume),
            'tank_standard_angle': data_clean(vehicle_info.tank_standard_angle),
        }
        # 创建对象
        vehicle_info = VehicleInfoModel(**vehicle_info_dict)
        # 查询流水号是否存在，流水号是唯一不能重复的信息，插入前应该进行判断
        current_vehicle_serial_number = vehicle_info_dict.get('serial_number')
        result = self.session.query(VehicleInfoModel.serial_number).filter(
            VehicleInfoModel.serial_number == current_vehicle_serial_number).first()
        try:
            if result:
                vehicle_info_dict.pop('serial_number')
                self.session.query(VehicleInfoModel).filter(
                    VehicleInfoModel.serial_number == current_vehicle_serial_number).update(vehicle_info_dict)
                logger.info(f'更新车辆信息：流水号>>>{current_vehicle_serial_number}')
            else:
                self.session.add(vehicle_info)
                logger.info(f'创建车辆信息：流水号>>>{current_vehicle_serial_number}')
            self.session.commit()
            updated_or_created_object = self.session.query(VehicleInfoModel).filter(
                VehicleInfoModel.serial_number == current_vehicle_serial_number).first()
            if not updated_or_created_object:
                raise CustomDBException(status_code=1000,
                                        message=f'流水号>>>{current_vehicle_serial_number}未获取创建对象，写入数据库失败')
            logger.info(f'创建/更新车辆信息：流水号>>>{current_vehicle_serial_number},写入数据库成功！')
            return updated_or_created_object
        except Exception as e:
            logger.error(
                f'创建/更新车辆信息：流水号>>>{current_vehicle_serial_number}失败，错误信息：{e}, 上下文信息：{traceback.format_exc()}')
            self.session.rollback()
            return
        finally:
            self.session.close()

    def insert_measure_record(self, serial_number, measure_record_dict, inspection_category):
        """插入检测记录"""
        # 查询车辆信息对象
        vehicle_info_row = self.session.query(VehicleInfoModel.id).filter(
            VehicleInfoModel.serial_number == serial_number).first()
        if not vehicle_info_row:
            logger.error(f'未找到流水号：{serial_number}的车辆信息')
            raise CustomDBException(status_code=1000, message=f'未找到流水号：{serial_number}的车辆信息')
        # 查询检验类别对象
        inspection_category_row = self.session.query(InspectionCategoryModel.id).filter(
            InspectionCategoryModel.inspection_category == inspection_category).first()
        if not inspection_category_row:
            logger.error(f'未找到流水号：{serial_number}的检验类别')
            raise CustomDBException(status_code=1000, message=f'未找到流水号：{serial_number}的检验类别')
        # 查询上一次检验记录次数
        last_measure_record_row = self.session.query(MeasureRecordModel.inspection_count).filter(
            MeasureRecordModel.vehicle_info_id == vehicle_info_row.id).order_by(
            MeasureRecordModel.id.desc()).first()
        inspection_count = last_measure_record_row.inspection_count + 1 if last_measure_record_row else 1
        try:
            measure_record_obj = MeasureRecordModel(
                vehicle_info_id=vehicle_info_row.id,
                inspection_category_id=inspection_category_row.id,
                inspection_count=inspection_count,
                **measure_record_dict
            )
            self.session.add(measure_record_obj)
            self.session.commit()
            logger.info(
                f'流水号：{serial_number}检验记录写入数据库成功！检验次数：{measure_record_obj.inspection_count}')
            return measure_record_obj.id
        except Exception as e:
            self.session.rollback()
            logger.error(
                f'流水号：{serial_number}检验记录写入数据库失败！，错误信息：{e}, 上下文信息：{traceback.format_exc()}')
        finally:
            self.session.close()


class VehicleRecordQuery:
    """车辆信息、车辆检验记录查询"""
    session = Session()

    def query_measure_record(self, measure_record_id):
        query = self.session.query(MeasureRecordModel, VehicleInfoModel).join(
            VehicleInfoModel, MeasureRecordModel.vehicle_info).filter(
            MeasureRecordModel.id == measure_record_id)
        try:
            measure_record, vehicle_info = query.one()
            return measure_record, vehicle_info
        except NoResultFound:
            logger.error(f'未查询到id为{measure_record_id}检验记录')
            return None

    # 查询所有的检验记录
    def all_measure_record(self, serial_number=None, plate_number=None, vin=None, start_date=None, end_date=None):
        conditions = []  # 初始化查询条件列表
        if serial_number:
            conditions.append(VehicleInfoModel.serial_number == serial_number)
        if plate_number:
            conditions.append(VehicleInfoModel.plate_number == plate_number)
        if vin:
            conditions.append(VehicleInfoModel.vin == vin)
        if start_date and end_date:
            start_date = datetime.strptime(start_date, '%Y/%m/%d').date()
            end_date = datetime.strptime(end_date, '%Y/%m/%d').date()
            conditions.append(MeasureRecordModel.measure_time.between(start_date, end_date + timedelta(days=1)))  # 两者之间
            # conditions.append(MeasureRecordModel.measure_time <= end_date)
        # if start_date:
        #     start_date = datetime.strptime(start_date, '%Y/%m/%d').date()
        # if end_date:
        #     end_date = datetime.strptime(end_date, '%Y/%m/%d').date()
        #
        # conditions = [
        #     condition for condition in (
        #         VehicleInfoModel.serial_number == serial_number,
        #         VehicleInfoModel.plate_number == plate_number,
        #         VehicleInfoModel.vin == vin,
        #         MeasureRecordModel.measure_time >= start_date,
        #         MeasureRecordModel.measure_time <= end_date
        #     ) if condition is not None
        # ]

        if conditions:
            query = self.session.query(MeasureRecordModel).options(joinedload(MeasureRecordModel.vehicle_info)).join(
                VehicleInfoModel, MeasureRecordModel.vehicle_info_id == VehicleInfoModel.id
            ).filter(and_(*conditions))
        else:
            query = self.session.query(MeasureRecordModel).options(joinedload(MeasureRecordModel.vehicle_info)).join(
                VehicleInfoModel, MeasureRecordModel.vehicle_info_id == VehicleInfoModel.id
            )
        measure_record_list = [[measure_record.id,  # ID
                                measure_record.measure_time,
                                measure_record.vehicle_info.plate_number,  # 号牌号码
                                measure_record.vehicle_info.plate_type.plate_type,
                                measure_record.vehicle_info.serial_number,
                                measure_record.vehicle_info.vin,
                                measure_record.vehicle_info.vehicle_type.vehicle_type,
                                measure_record.measure_length,
                                measure_record.measure_width,
                                measure_record.measure_height,
                                measure_record.measure_wheelbase,
                                measure_record.measure_fence_depth,
                                measure_record.measure_curb_weigh,
                                measure_record.length_judge,
                                measure_record.width_judge,
                                measure_record.height_judge,
                                measure_record.wheelbase_judge,
                                measure_record.fence_depth_judge,
                                measure_record.curb_weigh_judge,
                                ] for measure_record in query.all()]
        return measure_record_list


if __name__ == '__main__':
    init_query = InitializationUiQuery()
    init_query.query_plate_abbreviations()
